#!/usr/bin/env python
#
# Post-processor for converting simulation results (see results.py for types)
# into a database file in sqlite3 format.
#
# CAUTION: This code is theoretically prone to attack via SQL injection and
# should NOT be used in an operational setting. DB-API parameter substitution
# was not powerful enough to make this code work using proper substitutions.
#
# Author:   Mike Murphy <mamurph@cs.clemson.edu>
# Revision: 23 September 2009
#
#   Copyright 2009 Clemson University
#
#   Licensed under the Apache License, Version 2.0 (the "License");
#   you may not use this file except in compliance with the License.
#   You may obtain a copy of the License at
#
#       http://www.apache.org/licenses/LICENSE-2.0
#
#   Unless required by applicable law or agreed to in writing, software
#   distributed under the License is distributed on an "AS IS" BASIS,
#   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#   See the License for the specific language governing permissions and
#   limitations under the License.
#
# This material is based upon work supported under a National Science
# Foundation Graduate Research Fellowship. Any opinions, findings,
# conclusions or recommendations expressed in this publication are those of
# the author(s) and do not necessarily reflect the views of the National
# Science Foundation.

'''
Post-processor for converting simulation results into a database file in
SQLite format, version 3. Results are NOT buffered in memory, but are written
directly to the database, so that swapping may be avoided for large result
sets. CAUTION: If the output database file exists prior to instantiating a
DBOutput instance with the same output filename, the existing database file
will be deleted.

WARNING: This code is theoretically prone to attack via SQL injection and
should not be used in production server environments. The necessary dynamic
queries used in this module could not be implemented using the safe
replacement mechanism available in the sqlite3 code as of Python 2.6, due to
limitations of the sqlite3 module.
'''

from kernel import Output
import os, os.path, sqlite3, sys, time


class DBOutput(Output):
   '''
   Output handler that writes results directly to a database in sqlite3
   format. Results are neither filtered nor buffered.
   '''
   def __init__(self, filename, commit_ivl=500, output=None):
      '''
      @param filename: Name of sqlite3 database to create. If this file is
                       found in the filesystem, it will be deleted first.
      @type filename: str
      @param commit_ivl: Frequency with which database writes should be
                         committed to the database, in units of result counts
      @type commit_ivl: int
      @param output: Optional kernel.Output (or subclass) instance to which
                     results will be piped after writing to the database
      '''

      Output.__init__(self, output)

      # Delete any existing database at the same path as the output, otherwise
      # errors may occur
      if os.path.exists(filename):
         os.remove(filename)

      self.connection = sqlite3.connect(filename)
      self.cursor = self.connection.cursor()
      self.tables = []
      self.columns = {}
      self.commit_interval = commit_ivl
      self.last_commit = 0
   #
   def __commit(self):
      '''
      Private method: commits database transactions
      '''
      self.connection.commit()
      self.last_commit = 0
   #
   def __check_commit(self):
      '''
      Private method: checks to see if the commit interval has been reached
      and commits transactions if necessary
      '''
      self.last_commit += 1
      if self.last_commit >= self.commit_interval:
         self.__commit()
   #
   def __create_table(self, table_name, columns, add_time):
      '''
      Private method: creates a new table (and commits)

      @param table_name: Name of the table to create (must be unique)
      @type table_name: str
      @param columns: Sequence of column names (strings) to add to the table;
                      each table must have at least one column
      @param add_time: Flag indicating whether or not the time column
                       should be added to the table
      @type add_time: bool
      '''
      print >> sys.stderr, 'Creating table:', table_name
      if (len(columns) == 0) and (not add_time):
         raise Exception('Table must have at least one column (or add_time)')
      col_time = []
      if add_time:
         col_time = ['time']
      col_time.extend(columns)
      col_str = '"' + '", "'.join(col_time) + '"'
      self.cursor.execute('CREATE TABLE "%s" (%s)' % (table_name, col_str))
      self.tables.append(table_name)
      self.columns[table_name] = columns[:]
      self.__commit()
   #
   def __add_column(self, table_name, column_name):
      '''
      Private method: adds a column to a table. The column name must be unique,
      and the table must already exist.

      @param table_name: Table name
      @type table_name: str
      @param column_name: Name of the column to add
      @type column_name: str
      '''
      self.cursor.execute('ALTER TABLE "%s" ADD COLUMN "%s"' % \
                          (table_name, column_name))
      self.columns[table_name].append(column_name)
      self.__commit()
   #
   def __insert(self, table_name, columns, data):
      '''
      Private method: inserts data into a table

      @param table_name: Name of the table into which to insert
      @type table_name: str
      @param columns: Sequence of column names (str) into which data values
                      will be inserted
      @param data: Sequence of data values (sqlite3-supported types) IN THE
                   SAME ORDER AS THE COLUMN NAMES
      '''
      subs = ','.join([ '?' for x in xrange(0, len(columns)) ])
      query = 'INSERT INTO "' + table_name + '" ("'
      query += '", "'.join(columns)
      query += '") VALUES (' + subs + ')'
      self.cursor.execute(query, tuple(data))
   #
   def __add_to_db(self, table_name, result):
      '''
      Private method: adds a result to a table. Result objects are unpacked
      to populate table columns using result field names. If the table name is
      not found in the database, then a new table will be created. Similarly,
      if a result field name does not match a column name in the table, then a
      new column will be inserted.

      @param table_name: Name of the table in which to store data
      @type table_name: str
      @param result: Result object to unpack
      '''
      columns = result.fields.keys()
      data = result.fields.values()
      ts = result.time
      if table_name not in self.tables:
         self.__create_table(table_name, columns, (ts is not None))
      for key in result.fields:
         if key not in self.columns[table_name]:
	    self.__add_column(table_name, key)
      #
      if ts is not None:
         columns.insert(0, 'time')
         data.insert(0, ts)
      self.__insert(table_name, columns, data)
   #
   def handle_append(self, result):
      '''
      Adds an output result to the database

      @param result: Result object to unpack into the database
      '''
      rtype = result.result_type
      self.__add_to_db(rtype, result)
   #
   def handle_close(self):
      '''
      Commits final transactions and closes the database connection
      '''
      self.__commit()
      self.cursor.close()
      self.connection.close()
#
